﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Ccit.OA.Utils;
using Ccit.Utils;
using System.Collections.Generic; 
using Ccit.Global;
using System.Collections; 
using System.Text;
using Ccit.OA.MsAddrBook;
namespace Ccit.OA.AddrBook
{
    public class AddrBookSQL:IAddrBook
    {
        #region 获取个人通讯录列表信息
        /// <summary>
        /// 获取个人通讯录列表信息
        /// </summary>
        /// <param name="Query"></param>
        /// <returns></returns>
        public DataSet GetAddBookMes(string Query)
        {
            LoginUser login = (LoginUser)HttpContext.Current.Session["LogUserInfo"];
            if (Query != "")
            {
                Query = " and (ADDR_A1_30 like'%" + Query + "%' or ADDR_A1_70 like'%" + Query + "%' "+
                    "  or ADDR_A1_80 like'%" + Query + "%' or ADDR_A1_90 like'%" + Query + "%' or "+
                    "  ADDR_A1_100 like'%" + Query + "%' or  ADDR_A1_110 like'%" + Query + "%' or "+
                    "  ADDR_A1_120 like'%" + Query + "%' or ADDR_A1_180 like'%" + Query + "%' or " +
                    "  ADDR_A1_160 like'%" + Query + "%' or ADDR_A1_170 like'%" + Query + "%' or "+
                    "  ADDR_A1_140 like'%" + Query + "%' or ADDR_A1_200 like'%" + Query + "%' )";
            }
            string cmdText = " select ADDR_A1_60 as GroupId,ADDR_A1_10 as PKID,BAS_A1_50 as BZ,ADDR_A1_30 as [Name] ,ADDR_A1_70 as Company, ";
            cmdText += " ADDR_A1_100 as Telphone,ADDR_A1_110 as ComTel,ADDR_A1_120 as FTel,ADDR_A1_180 as email,ADDR_A2_30 as AGroupName from  ADDR_A1";
            cmdText += " left join ADDR_A2 on ADDR_A1_60 = ADDR_A2_10 left join BAS_A1 ON ADDR_A1_190 = BAS_A1_30 ";
            cmdText += " where ADDR_A1_20=" + login.UserId + " " + Query + "  order by ADDR_A1_30";
            DataSet dSet = DataAccessor.ExecuteDataSet(cmdText);
            return dSet;
        }
 
        #endregion 
        #region 删除个人通讯录信息
        /// <summary>
        /// 删除个人通讯录信息
        /// </summary>
        /// <param name="sRecid"></param>
        /// <returns></returns>
        public string DoOperateBook(string sRecid)
        {
            string cmdText = "delete from ADDR_A1 where ADDR_A1_10 in("+sRecid+")";
            int iNum = DataAccessor.ExecuteNonQuery(cmdText);
            if(iNum>0)
            { return "1"; }
            else
            { return "-998"; }
        }

        #endregion

        #region 保存个人通讯录信息
        /// <summary>
        /// 保存个人通讯录信息
        /// </summary>
        /// <param name="ms"></param>
        /// <returns></returns>
        public List<string> DoSaveAddBookMes(MsAddBooks ms)
        {
            LoginUser login = (LoginUser)HttpContext.Current.Session["LogUserInfo"];
            string cmdText = "";
            List<string> lstResult = new List<string>();
            if (ms.ID == "")
            {
                ms.ID = DataInterface.GetMaxId("ADDR_A1", "ADDR_A1_10").ToString();
                cmdText = "insert into ADDR_A1(ADDR_A1_10,ADDR_A1_20,ADDR_A1_30,ADDR_A1_40,ADDR_A1_50,ADDR_A1_60,ADDR_A1_70,"+
                        "ADDR_A1_80,ADDR_A1_90,ADDR_A1_100,ADDR_A1_110,ADDR_A1_120,ADDR_A1_130,ADDR_A1_140,ADDR_A1_150,ADDR_A1_160,"+
                        "ADDR_A1_170,ADDR_A1_180,ADDR_A1_190,ADDR_A1_200,ADDR_A1_210) values(" + ms.ID + "," + login.UserId + ",'" + ms.Name.Replace("'", "''") + "','" + ms.Call.Replace("'", "''") + "'," +
                        "'"+ms.Birthday.Replace("'","''")+"','"+ms.GroupId+"','"+ms.Company.Replace("'","''")+"','"+ms.DeptName.Replace("'","''")+"',"+
                        "'" + ms.PostName.Replace("'", "''") + "','"+ms.Telphone+"','"+ms.Comtel.Replace("'","''")+"',"+
                        "'"+ms.Ftel.Replace("'","''")+"','"+ms.Fax.Replace("'","''")+"','"+ms.Maddress.Replace("'","''")+"','"+ms.Postcode+"',"+
                        "'"+ms.msnname.Replace("'","''")+"','"+ms.qname.Replace("'","''")+"','"+ms.email.Replace("'","''")+"','"+ms.makenum+"',"+
                        "'"+ms.url.Replace("'","''")+"','"+ms.remark.Replace("'","''")+"')";
            }
            else
            {
                cmdText = "update ADDR_A1  set ADDR_A1_30='" + ms.Name.Replace("'", "''") + "',ADDR_A1_40='" + ms.Call.Replace("'", "''") + "',ADDR_A1_50='" + ms.Birthday.Replace("'", "''") + "'," +
                        "ADDR_A1_60='" + ms.GroupId + "',ADDR_A1_70='" + ms.Company.Replace("'", "''") + "',ADDR_A1_80='" + ms.DeptName.Replace("'", "''") + "'," +
                        "ADDR_A1_90='" + ms.PostName.Replace("'", "''") + "',ADDR_A1_100='" + ms.Telphone + "',ADDR_A1_110='" + ms.Comtel.Replace("'", "''") + "'," +
                        "ADDR_A1_120='" + ms.Ftel.Replace("'", "''") + "',ADDR_A1_130='" + ms.Fax.Replace("'", "''") + "',ADDR_A1_140='" + ms.Maddress.Replace("'", "''") + "',"+
                        "ADDR_A1_150='" + ms.Postcode.Replace("'", "''") + "',ADDR_A1_160='" + ms.msnname.Replace("'", "''") + "',ADDR_A1_170='" + ms.qname.Replace("'", "''") + "',"+
                        "ADDR_A1_180='" + ms.email.Replace("'", "''") + "',ADDR_A1_190='" + ms.makenum + "',ADDR_A1_200='" + ms.url.Replace("'", "''") + "'," +
                        "ADDR_A1_210='" + ms.remark.Replace("'", "''") + "' where ADDR_A1_10="+ms.ID;
            }
            int iNum = DataAccessor.ExecuteNonQuery(cmdText);
            if (iNum > 0)
            {
                lstResult.Add(ms.ID);
                lstResult.Add("操作成功");
            }
            else
            {
                lstResult.Add("-999");
                lstResult.Add("操作失败,服务器忙请稍候再试!");
            }
            return lstResult;
        }

        #endregion 
        #region 获取编辑页面信息
        /// <summary>
        /// 获取编辑页面信息
        /// </summary>
        /// <param name="sRecId"></param>
        /// <returns></returns>
        public MsAddBooks GetEditAddBookMes(string sRecId)
        {
            string cmdText = " select BAS_A1_30 as BZ,ADDR_A1_30,ADDR_A1_40,ADDR_A1_50,ADDR_A1_60,ADDR_A1_70, ";
            cmdText += "ADDR_A1_80,ADDR_A1_90,ADDR_A1_100 ,ADDR_A1_110,ADDR_A1_120,ADDR_A1_130,ADDR_A1_140,ADDR_A1_150,ADDR_A1_160,ADDR_A1_170,ADDR_A1_180,";
            cmdText +="ADDR_A1_190,ADDR_A1_200 ,ADDR_A1_210 from ADDR_A1";
            cmdText += "  left join BAS_A1 ON ADDR_A1_190 = BAS_A1_30 ";
            cmdText += " where ADDR_A1_10=" + sRecId + "";
            MsAddBooks ms = null;
            using(IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
            {
                if(iReader.Read())
                {
                    ms = new MsAddBooks(null, null, iReader["ADDR_A1_30"].ToString(),iReader["ADDR_A1_40"].ToString(),
                                        iReader["ADDR_A1_50"].ToString(), iReader["ADDR_A1_60"].ToString(),
                                        null,iReader["ADDR_A1_70"].ToString(),
                                        iReader["ADDR_A1_80"].ToString(),iReader["ADDR_A1_90"].ToString(),iReader["ADDR_A1_100"].ToString()
                                        ,iReader["ADDR_A1_110"].ToString(),iReader["ADDR_A1_120"].ToString(),iReader["ADDR_A1_130"].ToString()
                                        ,iReader["ADDR_A1_140"].ToString(),iReader["ADDR_A1_150"].ToString(),iReader["ADDR_A1_160"].ToString(),
                                        iReader["ADDR_A1_170"].ToString(), iReader["ADDR_A1_180"].ToString(), null, iReader["BZ"].ToString(),
                                        iReader["ADDR_A1_200"].ToString(), iReader["ADDR_A1_210"].ToString());
                }
            }
            return ms;
        }

        #endregion

        #region 获取设置分组信息
        /// <summary>
        /// 获取设置分组信息
        /// </summary>
        /// <returns></returns>
        public List<MsAddBookDetail> GetAddBookGroupMes()
        {
            LoginUser login = (LoginUser)HttpContext.Current.Session["LogUserInfo"];
            string cmdText = "select ADDR_A2_10,ADDR_A2_20,ADDR_A2_30 from ADDR_A2 where ADDR_A2_20="+login.UserId+"";
            List<MsAddBookDetail> lstGroup = new List<MsAddBookDetail>();
            using(IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
            {
                while(iReader.Read())
                {
                    MsAddBookDetail ms = new MsAddBookDetail(iReader["ADDR_A2_10"].ToString(), iReader["ADDR_A2_20"].ToString()
                                                            , iReader["ADDR_A2_30"].ToString());
                    lstGroup.Add(ms);
                }
            }
            return lstGroup;
        }

        #endregion

        #region 保存设置分组信息
        /// <summary>
        /// 保存设置分组信息
        /// </summary>
        /// <param name="ms"></param>
        /// <returns></returns>
        public string DoSaveGroupMes(MsAddBookDetail ms)
        {
            LoginUser login = (LoginUser)HttpContext.Current.Session["LogUserInfo"];
            string cmdText = "";
            if (ms.ID == "")
            {
                ms.ID = DataInterface.GetMaxId("ADDR_A2", "ADDR_A2_10").ToString();
                cmdText = "insert into ADDR_A2(ADDR_A2_10,ADDR_A2_20,ADDR_A2_30) values(" + ms.ID + "," + login.UserId + ",'" + ms.GroupName.Replace("'", "''") + "')";
            }
            else
            {
                cmdText = "update ADDR_A2 set ADDR_A2_30 ='" + ms.GroupName.Replace("'", "''") + "' where ADDR_A2_10="+ms.ID;
            }
            int iNum = DataAccessor.ExecuteNonQuery(cmdText);
            if (iNum > 0)
            { return "1"; }
            else {
                return "-999";
            }
        }

        #endregion

        #region IAddrBook 成员
        /// <summary>
        /// 修改个人通讯录分组信息
        /// </summary>
        /// <param name="sRecId"></param>
        /// <returns></returns>
        public string DaUpdateAddBookGroup(MsAddBooks ms)
        {
            string cmdText = "update ADDR_A1 set ADDR_A1_60 = " + ms.GroupId + " where ADDR_A1_10 in("+ms.ID+")";
            int iNum = DataAccessor.ExecuteNonQuery(cmdText);
            if(iNum>0)
            {return "1";}
            else
            { return "-999"; }
        }
        /// <summary>
        /// 删除分组信息
        /// </summary>
        /// <param name="sRecId"></param>
        /// <returns></returns>
        public string DaOperateGroup(string sRecId)
        {
            string cmdText = "delete ADDR_A2 where ADDR_A2_10 ="+sRecId;
            int iNum = DataAccessor.ExecuteNonQuery(cmdText);
            if (iNum > 0)
            { return "2"; }
            else
            { return "-999"; }
        }

        #endregion

        #region 获取导出Execl数据
        /// <summary>
        /// 获取导出Execl数据
        /// </summary>
        /// <param name="sRecId"></param>
        /// <returns></returns>
        public List<MsAddBooks> GetAddBooksExcelMes(string sRecId)
        {
            string cmdText = " select ADDR_A1_20,ADDR_A1_30,ADDR_A1_40,ADDR_A1_50,ADDR_A1_60,ADDR_A1_70, ";
            cmdText += "ADDR_A1_80,ADDR_A1_90,ADDR_A1_100 ,ADDR_A1_110,ADDR_A1_120,ADDR_A1_130,ADDR_A1_140,ADDR_A1_150,ADDR_A1_160,ADDR_A1_170,ADDR_A1_180,";
            cmdText += "ADDR_A1_190,ADDR_A1_200 ,ADDR_A1_210 from ADDR_A1"; 
            cmdText += " where ADDR_A1_10 in(" + sRecId + ")"; 
            List<MsAddBooks> lstAddBooks = new List<MsAddBooks>();
            using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
            {
                while (iReader.Read())
                {
                    MsAddBooks ms = new MsAddBooks(null, iReader["ADDR_A1_20"].ToString(), iReader["ADDR_A1_30"].ToString(),iReader["ADDR_A1_40"].ToString(),
                                        iReader["ADDR_A1_50"].ToString(), iReader["ADDR_A1_60"].ToString(),
                                        null, iReader["ADDR_A1_70"].ToString(),
                                        iReader["ADDR_A1_80"].ToString(), iReader["ADDR_A1_90"].ToString(), iReader["ADDR_A1_100"].ToString()
                                        , iReader["ADDR_A1_110"].ToString(), iReader["ADDR_A1_120"].ToString(), iReader["ADDR_A1_130"].ToString()
                                        , iReader["ADDR_A1_140"].ToString(), iReader["ADDR_A1_150"].ToString(), iReader["ADDR_A1_160"].ToString(),
                                        iReader["ADDR_A1_170"].ToString(), iReader["ADDR_A1_180"].ToString(), null, iReader["ADDR_A1_190"].ToString(),
                                        iReader["ADDR_A1_200"].ToString(), iReader["ADDR_A1_210"].ToString());
                    lstAddBooks.Add(ms);
                }
            }
            return lstAddBooks;
        }

        #endregion

        #region 保存excel导入的数据
        /// <summary>
        /// 保存excel导入的数据
        /// </summary>
        /// <param name="lstAddBooks"></param>
        /// <returns></returns>
        public string SaveImportExcelMes(List<MsAddBooks> lstAddBooks)
        {
            List<string> lstResult = new List<string>();
            string cmdText = "";
            if (lstAddBooks.Count > 0)
            {
                int maxId = DataInterface.GetMaxId("ADDR_A1", "ADDR_A1_10");
                for (int i = 0; i < lstAddBooks.Count;i++ )
                {
                    MsAddBooks ms = lstAddBooks[i];
                    cmdText = "insert into ADDR_A1 values(" + maxId + "," + ms.UserId + ",'" + ms.Name.Replace("'", "''") + "','" + ms.Call.Replace("'", "''") + "'," +
                       "'" + ms.Birthday.Replace("'", "''") + "','" + ms.GroupId + "','" + ms.Company.Replace("'", "''") + "','" + ms.DeptName.Replace("'", "''") + "'," +
                       "'" + ms.PostName.Replace("'", "''") + "','" + ms.Telphone + "','" + ms.Comtel.Replace("'", "''") + "'," +
                       "'" + ms.Ftel.Replace("'", "''") + "','" + ms.Fax.Replace("'", "''") + "','" + ms.Maddress.Replace("'", "''") + "','" + ms.Postcode + "'," +
                       "'" + ms.msnname.Replace("'", "''") + "','" + ms.qname.Replace("'", "''") + "','" + ms.email.Replace("'", "''") + "','" + ms.makenum + "'," +
                       "'" + ms.url.Replace("'", "''") + "','" + ms.remark.Replace("'", "''") + "')";
                    lstResult.Add(cmdText);
                    maxId++;
                }
            }
            int iNum = DataAccessor.ExecuteNonQuery(lstResult);
            if(iNum>0)
            { return "1"; }
            else
            { return "-998"; }
        }

        #endregion
    }
}
